<%-- 
    Document   : teste
    Created on : 16/09/2014, 17:33:32
    Author     : luiz
--%>

<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.io.PrintWriter"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body><%

        Connection conn = null;
        Statement stmt = null;
        String parametro = request.getParameter("cep");
        try {
            // JDBC driver name and database URL
            final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
            final String DB_URL = "jdbc:mysql://localhost:3306/cep.gpbe.17.01.2014";

            //  Database credentials
            final String USER = "root";
            final String PASS = "17145001";

            // Set response content type
            response.setContentType(
                    "text/html");

            String title = "Database Result";
            String docType
                    = "<!doctype html public \"-//w3c//dtd html 4.0 "
                    + "transitional//en\">\n";

            out.println(docType
                    + "<html>\n"
                    + "<head></head>\n"
                    + "<body>\n"
            );
            try {
                // Register JDBC driver
                Class.forName("com.mysql.jdbc.Driver");

                // Open a connection
                conn = DriverManager.getConnection(DB_URL, USER, PASS);
                stmt = conn.createStatement();

                String sql;
                sql = "SELECT log_logradouro.log_tipo_logradouro as tipo, log_logradouro.log_no as logradouro, log_bairro.bai_no as bairro, log_localidade.loc_no as cidade, log_localidade.ufe_sg as uf, log_logradouro.cep FROM log_logradouro join log_localidade on(log_logradouro.loc_nu_sequencial = log_localidade.loc_nu_sequencial) join log_bairro on (log_logradouro.bai_nu_sequencial_ini = log_bairro.bai_nu_sequencial) where log_logradouro.cep =" + parametro;
                ResultSet rs = stmt.executeQuery(sql);

                // Extract data from result set
                while (rs.next()) {
                    //Retrieve by column name

                    String tipo = rs.getString("tipo");
                    String logradouro = rs.getString("logradouro");
                    String bairro = rs.getString("bairro");
                    String cidade = rs.getString("cidade");
                    String uf = rs.getString("uf");
                    String cep = rs.getString("cep");

        %>

        <%                    }
                    out.println("</body></html>");

                    // Clean-up environment
                    rs.close();
                    stmt.close();
                    conn.close();
                } catch (SQLException se) {
                    //Handle errors for JDBC
                    se.printStackTrace();
                } catch (Exception e) {
                    //Handle errors for Class.forName
                    e.printStackTrace();
                } finally {
                    //finally block used to close resources
                    try {
                        if (stmt != null) {
                            stmt.close();
                        }
                    } catch (SQLException se2) {
                    }// nothing we can do
                    try {
                        if (conn != null) {
                            conn.close();
                        }
                    } catch (SQLException se) {
                        se.printStackTrace();
                    }//end finally try
                } //end try
            } finally {
                out.close();
            }
        %>
    </body>
</html>
